﻿using System;
using System.Collections.Generic;
using System.Text;
using IBatisNet.DataMapper;
using IBatisNet.DataMapper.Configuration;
using System.IO;
using System.Xml;
using System.Collections.Specialized;
using IBatisNet.Common.Exceptions;
using System.Collections;
using IBatisNet.Common.Pagination;
using System.Data;
using IBatisNet.DataMapper.Scope;
using IBatisNet.DataMapper.MappedStatements;
using IBatisNet.Common.Logging;
using IBatisNet.Common.Logging.Impl;
using IBatisNet.DataMapper.Configuration.Statements;
using IBatisNet.DataMapper.Configuration.Sql.Static;
using IBatisNet.DataMapper.Configuration.Sql;
using System.Data.Common;

namespace MyLib
{
    public class DataAccess
    {
        static Hashtable _table = new Hashtable();
        public static DataAccess Instance()
        {
            return Instance("");
        }

        public static DataAccess Instance(string dbName)
        {
            if (string.IsNullOrEmpty(dbName))
            {
                if (ConfigurationManager.ConnectionStrings.Count > 0)
                {
                    dbName = ConfigurationManager.ConnectionStrings[0].Name;
                }
                if (ConfigurationManager.ConnectionStrings.Count > 1)
                {
                    if (string.IsNullOrEmpty(ConfigurationManager.ConnectionStrings[1].ConnectionString.Trim()))
                    {
                        if (ConfigurationManager.ConnectionStrings.Count > 2)
                        {
                            dbName = ConfigurationManager.ConnectionStrings[2].Name;
                        }
                    }
                    else
                    {
                        dbName = ConfigurationManager.ConnectionStrings[1].Name;
                    }
                }

            }

            if (_table.ContainsKey(dbName))
            {
                return _table[dbName] as DataAccess;
            }
            //加日志组件
            NameValueCollection values = new NameValueCollection();
            values.Add("configType", "EXTERNAL");
            IBatisNet.Common.Logging.LogManager.Adapter = new Log4NetLoggerFA(values);

            DataAccess mapper = new DataAccess();
            System.Configuration.ConnectionStringSettings connstr = ConfigurationManager.ConnectionStrings[dbName];
            if (connstr == null)
            {
                throw new ArgumentNullException(dbName+" can not found in config!");
            }
            string providerName = connstr.ProviderName;
            if (string.IsNullOrEmpty(providerName) || providerName == "System.Data.SqlClient")
            {
                providerName = "sqlServer2.0";
            }
            string connectionString = connstr.ConnectionString;            
            mapper.Config(providerName, connectionString);
            _table.Add(dbName, mapper);
            LogUtil.Info(string.Format("数据库名称{0}，连接字符串{1}",dbName,connectionString));
            
            return mapper;
        }

        public static void ClearInstance()
        {
            _table.Clear();
        }

        ISqlMapper _sqlMap;
        ConfigurationScope _scope;
        internal DataAccess()
        {
            
        }

        void Config(string providerName, string connectionString, Type type, string embededSqlMapFile)
        {
            NameValueCollection properties = new NameValueCollection();
            properties.Add("providerName", providerName);
            properties.Add("connectionString", connectionString);

            DomSqlMapBuilder builder = new DomSqlMapBuilder();
            builder.Properties = properties;
            Stream stream = ResourceUtil.GetResourceStream(type, embededSqlMapFile);
            ISqlMapper sqlMap = builder.Configure(stream);
            _sqlMap = sqlMap;
            
            _scope = builder._configScope;
        }

        void ConfigByFile(string providerName, string connectionString,  string sqlMapFile)
        {
            NameValueCollection properties = new NameValueCollection();
            properties.Add("providerName", providerName);
            properties.Add("connectionString", connectionString);

            DomSqlMapBuilder builder = new DomSqlMapBuilder();
            builder.Properties = properties;
            ISqlMapper sqlMap = builder.Configure(sqlMapFile);
            _sqlMap = sqlMap;
            _scope = builder._configScope;
        }

        void Config(string providerName, string connectionString)
        {
            NameValueCollection properties = new NameValueCollection();
            properties.Add("providerName", providerName);
            properties.Add("connectionString", connectionString);

            DomSqlMapBuilder builder = new DomSqlMapBuilder();
            builder.Properties = properties;
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(SqlMapConfig.GetConfig());
            ISqlMapper sqlMap = builder.Configure(xmlDoc);
            _sqlMap = sqlMap;
            _scope = builder._configScope;

        }

        public ISqlMapper SqlMapper
        {
            get
            {
                return _sqlMap;
            }
        }

        public object Insert(string statementName,object parameterObject)
        {
            ISqlMapper sqlMap = SqlMapper;

            try
            {
                return sqlMap.Insert(statementName, parameterObject);
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing query '" + statementName + "' for insert.  Cause: " + e.Message, e);
            }
        }

        public int Update(string statementName, object parameterObject)
        {
            ISqlMapper sqlMap = SqlMapper;

            try
            {
                return sqlMap.Update(statementName, parameterObject);
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing query '" + statementName + "' for update.  Cause: " + e.Message, e);
            }
        }

        public int Delete(string statementName, object parameterObject)
        {
            ISqlMapper sqlMap = SqlMapper;

            try
            {
                return sqlMap.Delete(statementName, parameterObject);
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing query '" + statementName + "' for delete.  Cause: " + e.Message, e);
            }
        }

        public T GetEntity<T>(string statementName, object parameterObject)
        {
            ISqlMapper sqlMap = SqlMapper;

            try
            {
                return sqlMap.QueryForObject<T>(statementName, parameterObject);
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing query '" + statementName + "' for query object.  Cause: " + e.Message, e);
            }
        }

        public object GetObject(string statementName, object parameterObject)
        {
            ISqlMapper sqlMap = SqlMapper;

            try
            {
                return sqlMap.QueryForObject(statementName, parameterObject);
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing query '" + statementName + "' for query object.  Cause: " + e.Message, e);
            }
        }

        public T GetEntity<T>(string sqltext)
        {
            RegisterMappedStatement("ExecuteSelectSql", sqltext, typeof(T));
            return GetEntity<T>("ExecuteSelectSql", null);
        }

        public List<T> GetList<T>(string statementName, object parameterObject)
        {
            ISqlMapper sqlMap = SqlMapper;

            try
            {
                return (List<T>)sqlMap.QueryForList<T>(statementName, parameterObject);
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing query '" + statementName + "' for query list.  Cause: " + e.Message, e);
            }
        }

        public List<T> GetList<T>(string statementName, object parameterObject, int skipResults, int maxResults)
        {
            ISqlMapper sqlMap = SqlMapper;

            try
            {
                return (List<T>)sqlMap.QueryForList<T>(statementName, parameterObject, skipResults, maxResults);
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing query '" + statementName + "' for query list.  Cause: " + e.Message, e);
            }
        }

        public List<T> GetList<T>(string sqltext)
        {
            RegisterMappedStatement("ExecuteSelectSql", sqltext, typeof(T));
            return GetList<T>("ExecuteSelectSql", null);
        }

        public List<T> GetList<T>(string sqltext, int skipResults, int maxResults)
        {
            RegisterMappedStatement("ExecuteSelectSql", sqltext, typeof(T));
            return GetList<T>("ExecuteSelectSql", null, skipResults, maxResults);
        }

        void RegisterMappedStatement(string id,string sql,Type resultClass)
        {
            ISqlMapper sqlMap = SqlMapper;
            if (sqlMap.MappedStatements.Contains(id))
            {
                sqlMap.MappedStatements.Remove(id);
            }

            Statement statement = new Statement();
            statement.Id = id;
            statement.ParameterClassName = "string";
            statement.ResultClassName = resultClass.FullName;
            statement.AllowRemapping = true;
            ConfigurationScope scope = _scope;
            ISql isql = new StaticSql(scope, statement);
            ISqlMapSession session = new SqlMapSession(sqlMap);
            ((StaticSql)isql).BuildPreparedStatement(session, sql);

            statement.Initialize(scope);
            statement.Sql = isql;

            SelectMappedStatement mapStatement = new SelectMappedStatement(sqlMap, statement);
            sqlMap.AddMappedStatement(statement.Id, mapStatement);
        }


        public IDbTransaction BeginTransaction()
        {
            try
            {
                IDbCommand cmd= GetDbCommand();
                if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection.Open();
                }
                return cmd.Connection.BeginTransaction();
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing  begin trans.  Cause: " + e.Message, e);
            }
        }

        public void CommitTransaction(IDbTransaction trans)
        {
            try
            {
                trans.Commit();
                if (trans.Connection != null && trans.Connection.State == ConnectionState.Open)
                {
                    trans.Connection.Close();
                }
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing   commit trans.  Cause: " + e.Message, e);
            }
        }

        public void RollBackTransaction(IDbTransaction trans)
        {
            try
            {
                trans.Rollback();
                if (trans.Connection!=null && trans.Connection.State == ConnectionState.Open)
                {
                    trans.Connection.Close();
                }
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing  rollback trans.  Cause: " + e.Message, e);
            }
        }

        public DataSet GetDataSet(string statementName, object paramObject)
        {
            try
            {
                DataSet ds = new DataSet();
                ISqlMapper mapper = SqlMapper;
                IMappedStatement statement = mapper.GetMappedStatement(statementName);
                if (!mapper.IsSessionStarted)
                {
                    mapper.OpenConnection();
                }
                RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, mapper.LocalSession);
                statement.PreparedCommand.Create(scope, mapper.LocalSession, statement.Statement, paramObject);

                IDbCommand dc = mapper.LocalSession.CreateCommand(scope.IDbCommand.CommandType);
                dc.CommandText = scope.IDbCommand.CommandText;
                foreach (IDbDataParameter para in scope.IDbCommand.Parameters)
                {
                    IDbDataParameter p = mapper.LocalSession.CreateDataParameter();
                    p.DbType = para.DbType;
                    p.Direction = para.Direction;
                    p.ParameterName = para.ParameterName;
                    p.Precision = para.Precision;
                    p.Scale = para.Scale;
                    p.Size = para.Size;
                    p.SourceColumn = para.SourceColumn;
                    p.SourceVersion = para.SourceVersion;
                    p.Value = para.Value;
                    dc.Parameters.Add(p);
                }
                IDbDataAdapter dda = mapper.LocalSession.CreateDataAdapter(dc);
                dda.Fill(ds);
                foreach (IDbDataParameter para in scope.IDbCommand.Parameters)
                {
                    if (para.Direction == ParameterDirection.Output)
                    {
                        if (paramObject is Hashtable)
                        {
                            Hashtable ht = (Hashtable)paramObject;
                            ht[para.ParameterName.Replace("@", "")] = ((System.Data.Common.DbParameter)(dc.Parameters[para.ParameterName])).Value;
                        }
                    }
                }
                return ds;
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw new IBatisNetException("Error executing query '" + statementName + "' for DataSet.  Cause: " + e.Message, e);
            }
        }

        public IDbCommand GetDbCommand()
        {
            try
            {
                ISqlMapper mapper = SqlMapper;
                if (!mapper.IsSessionStarted)
                {
                    mapper.OpenConnection();
                }
                IDbCommand dc = mapper.LocalSession.CreateCommand(CommandType.Text);
                return dc;
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw e;
            }
        }

        public IDbCommand GetDbCommand(ref string parameterPrefix)
        {
            try
            {
                ISqlMapper mapper = SqlMapper;
                if (!mapper.IsSessionStarted)
                {
                    mapper.OpenConnection();
                }
                IDbCommand dc = mapper.LocalSession.CreateCommand(CommandType.Text);
                parameterPrefix=mapper.LocalSession.DataSource.DbProvider.ParameterPrefix;
                return dc;
            }
            catch (Exception e)
            {
                LogUtil.Error(e);
                throw e;
            }
        }

        public int ExecuteNonQuery(string sqltext)
        {
            IDbCommand cmd = GetDbCommand();
            cmd.CommandText = sqltext;
            int i = 0;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            i= cmd.ExecuteNonQuery();
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
            return i;
        }

        public int ExecuteNonQuery(string sqltext, List<DataParameter> parameters)
        {
            string parameterPrefix = "@";
            IDbCommand cmd = GetDbCommand(ref parameterPrefix);
            cmd.CommandText = sqltext.Replace("@", parameterPrefix);
            cmd.Parameters.Clear();
            foreach (DataParameter para in parameters)
            {
                IDataParameter dbParameter = cmd.CreateParameter();
                dbParameter.ParameterName = ConvertUtil.ToString(para.ParameterName).Replace("@", parameterPrefix);
                dbParameter.Value = para.Value;
                dbParameter.DbType = para.DbType;
                cmd.Parameters.Add(dbParameter);
            }
            int i = 0;
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            i = cmd.ExecuteNonQuery();
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
            return i;
        }

        public T ExecuteScalar<T>(string sqltext)
        {
            IDbCommand cmd = GetDbCommand();
            cmd.CommandText = sqltext;
            
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            object obj = cmd.ExecuteScalar();
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
            if (obj == null || obj == DBNull.Value)
            {
                return default(T);
            }
            return (T)obj;
        }

        public object ExecuteScalar(string sqltext)
        {
            IDbCommand cmd = GetDbCommand();
            cmd.CommandText = sqltext;

            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            object obj = cmd.ExecuteScalar();
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
            
            return obj;
        }

        public object ExecuteScalar(string sqltext, List<DataParameter> parameters)
        {
            string parameterPrefix = "@";
            IDbCommand cmd = GetDbCommand(ref parameterPrefix);
            cmd.CommandText = sqltext.Replace("@", parameterPrefix);
            cmd.Parameters.Clear();
            foreach (DataParameter para in parameters)
            {
                IDataParameter dbParameter = cmd.CreateParameter();
                dbParameter.ParameterName = ConvertUtil.ToString(para.ParameterName).Replace("@", parameterPrefix);
                dbParameter.Value = para.Value;
                dbParameter.DbType = para.DbType;
                cmd.Parameters.Add(dbParameter);
            }
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            object obj = cmd.ExecuteScalar();
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }

            return obj;
        }

        public DataSet ExecuteDataSet(string sqltext)
        {
            IDbCommand cmd = GetDbCommand();
            cmd.CommandText = sqltext;
            ISqlMapper mapper = SqlMapper;
            IDbDataAdapter adap = mapper.LocalSession.CreateDataAdapter(cmd);
            DataSet ds=new DataSet();
            adap.Fill(ds);
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
            return ds;
        }

        public DataSet ExecuteDataSet(string sqltext, List<DataParameter> parameters)
        {
            string parameterPrefix = "@";
            IDbCommand cmd = GetDbCommand(ref parameterPrefix);
            cmd.CommandText = sqltext.Replace("@", parameterPrefix);
            cmd.Parameters.Clear();
            foreach (DataParameter para in parameters)
            {
                IDataParameter dbParameter = cmd.CreateParameter();
                dbParameter.ParameterName = ConvertUtil.ToString(para.ParameterName).Replace("@", parameterPrefix);
                dbParameter.Value = para.Value;
                dbParameter.DbType = para.DbType;
                cmd.Parameters.Add(dbParameter);
            }
            ISqlMapper mapper = SqlMapper;
            IDbDataAdapter adap = mapper.LocalSession.CreateDataAdapter(cmd);
            DataSet ds=new DataSet();
            adap.Fill(ds);
            if (cmd.Connection.State == ConnectionState.Open)
            {
                cmd.Connection.Close();
            }
            return ds;
            
        }

        public IDataReader ExecuteDataReader(string sqltext)
        {
            IDbCommand cmd = GetDbCommand();
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            cmd.CommandText = sqltext;
            return cmd.ExecuteReader();
        }

        public IDataReader ExecuteDataReader(string sqltext, List<DataParameter> parameters)
        {
            string parameterPrefix = "@";
            IDbCommand cmd = GetDbCommand(ref parameterPrefix);
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
            cmd.CommandText = sqltext.Replace("@", parameterPrefix);
            cmd.Parameters.Clear();
            foreach (DataParameter para in parameters)
            {
                IDataParameter dbParameter = cmd.CreateParameter();
                dbParameter.ParameterName = ConvertUtil.ToString(para.ParameterName).Replace("@", parameterPrefix);
                dbParameter.Value = para.Value;
                dbParameter.DbType = para.DbType;
                cmd.Parameters.Add(dbParameter);
            }
            return cmd.ExecuteReader();
        }

        public DataTable ExecuteDataTable(string sqltext)
        {
            DataSet ds = ExecuteDataSet(sqltext);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return new DataTable();
        }

        public DataTable ExecuteDataTable(string sqltext,List<DataParameter> parameters)
        {
            DataSet ds = ExecuteDataSet(sqltext,parameters);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return new DataTable();
        }

        public int ExecuteNonQuery(IDbTransaction trans, string sqltext)
        {
            IDbCommand cmd = trans.Connection.CreateCommand();
            cmd.Transaction = trans;
            cmd.CommandText = sqltext;
            int i = 0;            
            i = cmd.ExecuteNonQuery();
            return i;
        }

        public T ExecuteScalar<T>(IDbTransaction trans, string sqltext)
        {
            IDbCommand cmd = trans.Connection.CreateCommand();
            cmd.Transaction = trans;
            cmd.CommandText = sqltext;            
            object obj = cmd.ExecuteScalar();           
            if (obj == null || obj == DBNull.Value)
            {
                return default(T);
            }
            return (T)obj;
        }

        public object ExecuteScalar(IDbTransaction trans, string sqltext)
        {
            IDbCommand cmd = trans.Connection.CreateCommand();
            cmd.Transaction = trans;
            cmd.CommandText = sqltext;
            object obj = cmd.ExecuteScalar();
            
            return obj;
        }

        public DataSet ExecuteDataSet(IDbTransaction trans, string sqltext)
        {
            IDbCommand cmd = trans.Connection.CreateCommand();
            cmd.Transaction = trans;
            cmd.CommandText = sqltext;
            ISqlMapper mapper = SqlMapper;
            IDbDataAdapter adap = mapper.LocalSession.CreateDataAdapter(cmd);
            DataSet ds = new DataSet();
            adap.Fill(ds);
            return ds;
        }

        public IDataReader ExecuteDataReader(IDbTransaction trans, string sqltext)
        {
            IDbCommand cmd = trans.Connection.CreateCommand();
            if (trans.Connection.State == ConnectionState.Closed)
            {
                trans.Connection.Open();
            }
            cmd.Transaction = trans;
            cmd.CommandText = sqltext;
            return cmd.ExecuteReader();
        }

        public DataTable ExecuteDataTable(IDbTransaction trans, string sqltext)
        {
            DataSet ds = ExecuteDataSet(trans,sqltext);
            if (ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return new DataTable();
        }


        public string GetSqlString(string statementName, object paramObject)
        {
            ISqlMapper mapper = SqlMapper;
            IMappedStatement statement = mapper.GetMappedStatement(statementName);
            if (!mapper.IsSessionStarted)
            {
                mapper.OpenConnection();
            }
            RequestScope scope = statement.Statement.Sql.GetRequestScope(statement, paramObject, mapper.LocalSession);

            return scope.PreparedStatement.PreparedSql;
        }

    }
}
